﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Rison.DataAccess.Common;
using Rison.DBHelper;

namespace Rison.DataAccess.Data.Biz
{
    public sealed class InfosReaderDb : DataAccessBase
    {
        public InfosReaderDb()
            : base()
        {
        }

        public InfosReaderDb(string tempDbConnStr)
            : base()
        {
            //设置第三方数据库的连接串
            base.SetTempConnectionString(tempDbConnStr);
        }

        protected override string tableName
        {
            get { return "Biz_CPResultDetail"; }
        }

        protected override string IdentityFieldName
        {
            get { return "RDID"; }
        }

        public DataRow ReadInfomation(string infoId)
        {
            return base.ExecuteSqlToDataRow("select title,showTime,content,MediaFileName from Information where infoGuid='" + infoId + "'");
        }

        public DataSet ReadNewsInfos(string categoryId, int topNum, int subjectLength, bool containContent)
        {
            return ReadNewsInfos(categoryId, topNum, subjectLength, containContent, string.Empty);
        }

        public DataSet ReadNewsInfos(string categoryId, int topNum, int subjectLength, bool containContent,string condition)
        {
            StringBuilder sql = new StringBuilder();
            if (topNum > 1)
                sql.AppendFormat("select top {0} infoGuid as infoId,ShowTime as day,MediaFileName,", topNum.ToString());
            else
                sql.Append("select infoGuid as infoId,ShowTime as day,MediaFileName,");

            if (containContent)
                sql.Append("ContentNoHTML,");

            //sql.AppendFormat("title=case when (datalength(title)>{0}*2) then dbo.fn_substringSubject(title,{1}) else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            if (subjectLength > 1)
                sql.AppendFormat("title=case when (datalength(title)>{0}*2) then SUBSTRING(title,1,{1})+'..' else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            else
                sql.Append("title ");

            sql.AppendFormat("from vwInfoCategory_Show where CategoryID='{0}' ", categoryId);

            if (!string.IsNullOrEmpty(condition))
                sql.AppendFormat(" and {0} ", condition);

            sql.Append("Order by BackInt,ShowTime DESC,CreateTime DESC");

            DataSet data = ExecuteSql(sql.ToString(), "NewsInfos");

            sql = null; return data;
        }



        public DataRow ReadSuqiuDetail(string requestId)
        {
            return base.ExecuteSqlToDataRow("select a.*,b.MailBoxDisplay from Request a left outer join MailBox b on a.SendToTag=b.ID where a.id='" + requestId + "'");
        }



        //最新诉求
        public DataSet ReadSuqiuInfos(int topNum, int subjectLength)
        {
            StringBuilder sql = new StringBuilder();

            if (topNum > 1)
                sql.AppendFormat("select top {0} ID as infoId,Type,SubmitTime as day,", topNum.ToString());
            else
                sql.Append("select ID as infoId,Type,SubmitTime as day,");

            if (subjectLength > 1)
                sql.AppendFormat("title=case when (datalength(title)>{0}*2) then SUBSTRING(title,1,{1})+'..' else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            else
                sql.Append("title ");

            sql.Append("from Request where requestType='我有诉求' and ispublished=1 Order by SubmitTime DESC");

            DataSet data = ExecuteSql(sql.ToString(), "SuqiuInfos");

            sql = null; return data;
        }


        //诉求的最新回复
        public DataSet ReadSuqiuRepeat(int topNum, int subjectLength)
        {
            StringBuilder sql = new StringBuilder();

            if (topNum > 1)
                sql.AppendFormat("select top {0} ID as infoId,Type,CompleteTime as day,", topNum.ToString());
            else
                sql.Append("select ID as infoId,Type,CompleteTime as day,");

            if (subjectLength > 1)
                sql.AppendFormat("title=case when (datalength(title)>{0}*2) then SUBSTRING(title,1,{1})+'..' else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            else
                sql.Append("title ");

            sql.Append("from Request where CompleteTime is not null ");

            sql.Append("Order by CompleteTime DESC");

            DataSet data = ExecuteSql(sql.ToString(), "SuqiuRepeat");

            sql = null; return data;

        }

        public DataSet ReadVoteAnswer(string id)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select AnswerContent,VoteCount from ViewVote where VoteID='{0}' order by VoteCount desc", id);
            DataSet data = ExecuteSql(sql.ToString(), "ReadVoteAnswer");

            sql = null; return data;

        }

        //诉求的list表
        public DataSet ReadSuqiuList(string condition)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append("select a.id,a.title,a.SubmitTime,a.Type,b.MailBoxDisplay,a.CompleteTime from Request a ");
            sql.Append("left outer join MailBox b on a.SendToTag=b.ID ");
            sql.Append("where a.RequestType='我有诉求' and a.ispublished=1 ");
            //where 
            if (condition != "")
            {
                sql.Append(condition);
            }
            //排序 -按提交时间
            sql.Append(" order by a.SubmitTime");

            DataSet data = ExecuteSql(sql.ToString(), "ReadSuqiuList");

            sql = null; return data;
        }

        //诉求回复
        public DataSet SuQiuTJ()
        {
            StringBuilder sql = new StringBuilder();

            sql.Append("select MailBoxDisplay,zx,ts,jy,sq, ");
            sql.Append("(select COUNT(1) from Request c where c.SendToTag=d.SendToTag and CompleteTime!='') as hf ");
            sql.Append("from  ");
            sql.Append("(select a.SendToTag, ");
            sql.Append("count(case when a.type='咨询' then a.id end) zx, ");
            sql.Append("count(case when a.type='投诉' then a.id end) ts, ");
            sql.Append("count(case when a.type='意见建议' then a.id end) jy, ");
            sql.Append("count(1) sq ");
            sql.Append("from Request a where a.RequestType='我有诉求' and a.ispublished=1 ");
            sql.Append("group by  a.SendToTag) d right join MailBox m on d.SendToTag=m.ID  ");
            sql.Append("inner join CategoryMailboxRel cat on m.ID = cat.MailBOxID  ");
            sql.Append("inner join Category on cat.CategoryID = Category.CategoryID  ");
            sql.Append(" where category.ParentCategoryID='5B1D83C8-18BF-40CB-A212-CB9E48F3B158'");
            //sql.Append("where Category.CategoryName='行业' ");
            //sql.Append("or Category.CategoryName='政府部门' ");
            sql.Append("order by Category.OrderNum,cat.OrderNum ");


            DataSet data = ExecuteSql(sql.ToString(), "SuQiuTJ");

            sql = null; return data;
        }

        public DataSet SuQiuTJ_show(int topNum, int subjectLength)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat("select top {0} m.id, " ,topNum.ToString());
            sql.Append("case m.ID ");
            sql.Append("when '917E7DB2-7DE2-49ED-8326-5C3BD6C2B369' then '区府办' ");
            sql.Append("when 'A4BA9348-3EC6-4761-AD8B-BE999976B436' then '区发改委' ");
            sql.Append("when '4B8E1D34-0414-4F9E-91B3-C3FCCA4841CE' then '区商务委' ");
            sql.Append("when '6018F75E-F67E-4B2C-B1F1-7BE2309D62FC' then '区建交委' ");
            sql.Append("when 'C3610045-99EC-4D23-9B8C-7444941EEC53' then '区科委' ");
            sql.Append("when '1C94C597-5316-4E28-87A5-A923B3B94952' then '区人口计生委' ");
            sql.Append("when '7CA71981-55D1-4427-A319-8301601000A0' then '区国资委' ");
            sql.Append("else MailBoxDisplay ");
            sql.Append("end as MailBoxDisplay, ");
            //sql.Append(" MailBoxDisplay, ");
            sql.Append("case when sq is null then 0 else sq end sq,Category.OrderNum,cat.OrderNum, ");
            sql.Append(" (select COUNT(1) from Request c where c.SendToTag=d.SendToTag and CompleteTime!='') as hf  ");
            sql.Append("from (select a.SendToTag, count(1) as sq  ");
            sql.Append(" from Request a where a.RequestType='我有诉求' and a.ispublished=1 group by  a.SendToTag) d  ");
           sql.Append(" right outer join MailBox m on d.SendToTag=m.ID   ");
           sql.Append(" inner join CategoryMailboxRel cat on m.ID = cat.MailBOxID   ");
           sql.Append(" inner join Category on cat.CategoryID = Category.CategoryID       ");
           sql.Append(" where category.ParentCategoryID='5B1D83C8-18BF-40CB-A212-CB9E48F3B158'    ");
           sql.Append(" order by Category.OrderNum,cat.OrderNum  ");


            DataSet data = ExecuteSql(sql.ToString(), "SuQiuTJ");

            sql = null; return data;
        }

        

    }
}